package com.ds.duizhang20221212;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSON;
import com.ds.duizhang20221212.dto.CheckOutExcelDTO;
import com.ds.duizhang20221212.dto.ErpRefundExcelDTO;
import com.ds.duizhang20221212.dto.export.ErpCheckoutExportExcelDTO;
import com.qianheng.kit.base.util.EasyExcelUtil;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.filefilter.RegexFileFilter;
import org.apache.commons.lang3.StringUtils;
import org.javatuples.Unit;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;

import java.io.File;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

import static org.apache.commons.io.filefilter.CanReadFileFilter.CAN_READ;

public class Handle {
    static Logger log = LoggerFactory.getLogger("duizhang");
    static SimpleDateFormat simpleDateFormatSource = new SimpleDateFormat("yyyy/M/dd");
    static SimpleDateFormat simpleDateFormatSource2 = new SimpleDateFormat("yyyy-MM-dd");
    static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd-HH-mm");

    static boolean logDetail = false;

    public static void main(String[] args) throws Exception {
        String rpcBasePath = "C:\\ds\\2022年12月12日\\退货退款-更新\\退款系统数据\\";
        String exportPath = "C:\\ds\\2022年12月12日\\退货退款-更新\\代码校验结果\\";
        String exportFileName = exportPath + "校验结果" + simpleDateFormat.format(new Date()) + ".xlsx";

        String file2020全部 = rpcBasePath + "return-refund(2022-12-05 16-50)-2020全部.xlsx";
        String file2021全部 = rpcBasePath + "return-refund(2022-12-05 16-52)-2021全部.xlsx";
        String file20220531全部 = rpcBasePath + "return-refund(2022-12-05 17-00)-20220531全部.xlsx";
        String file20221031全部 = rpcBasePath + "return-refund(2022-12-05 17-04)-20221031全部.xlsx";

        String file2020发货后 = rpcBasePath + "return-refund(2022-12-05 15-38)-2020发货后.xlsx";
        String file2021发货后 = rpcBasePath + "return-refund(2022-12-05 15-42)-2021发货后.xlsx";
        String file2022发货后 = rpcBasePath + "return-refund(2022-12-05 15-43)-2022发货后.xlsx";

        String file2020发货前退款全部 = rpcBasePath + "return-refund(2022-12-12 22-46)-2020发货前退款全部.xlsx";
        String file2021发货前退款全部 = rpcBasePath + "return-refund(2022-12-12 22-49)-2021发货前退款全部.xlsx";
        String file2022发货前退款截至10月31日 = rpcBasePath + "return-refund(2022-12-12 22-51)-2022发货前退款-截至10月31日.xlsx";

        String channelBasePath = "C:\\ds\\2022年12月12日\\退货退款-更新\\退款渠道\\";
        Collection<File> checkout渠道数据文件 = FileUtils.listFiles(new File(channelBasePath), new RegexFileFilter("checkout数据.*xlsx"), CAN_READ);
        Collection<File> ipaylink渠道数据文件 = FileUtils.listFiles(new File(channelBasePath), new RegexFileFilter("ipaylink.*xls"), CAN_READ);
        Collection<File> paypal渠道数据文件 = FileUtils.listFiles(new File(channelBasePath), new RegexFileFilter("paypal.*xlsx"), CAN_READ);
        Collection<File> Tabby渠道数据文件 = FileUtils.listFiles(new File(channelBasePath), new RegexFileFilter("Tabby退款.*xlsx"), CAN_READ);
        Collection<File> dlocal渠道数据文件 = FileUtils.listFiles(new File(channelBasePath), new RegexFileFilter("dlocal.*csv"), CAN_READ);
        System.out.println(checkout渠道数据文件);
        System.out.println(ipaylink渠道数据文件);
        System.out.println(paypal渠道数据文件);
        System.out.println(Tabby渠道数据文件);
        System.out.println(dlocal渠道数据文件);

        List<ErpRefundExcelDTO> erp发货后汇总 = new ArrayList<>();
        List<ErpRefundExcelDTO> erp发货前汇总 = new ArrayList<>();
        List<ErpRefundExcelDTO> erp全部汇总 = new ArrayList<>();
//        erp发货后汇总.addAll(readFirstSheet(file2020发货后, ErpRefundExcelDTO.class, 1, "erp 2020发货后"));
//        erp发货后汇总.addAll(readFirstSheet(file2021发货后, ErpRefundExcelDTO.class, 1, "erp file2021发货后"));
//        erp发货后汇总.addAll(readFirstSheet(file2022发货后, ErpRefundExcelDTO.class, 1, "erp file2022发货后"));
//
//        erp发货前汇总.addAll(readFirstSheet(file2020发货前退款全部, ErpRefundExcelDTO.class, 1, "erp file2020发货前退款全部"));
//        erp发货前汇总.addAll(readFirstSheet(file2021发货前退款全部, ErpRefundExcelDTO.class, 1, "erp file2021发货前退款全部"));
//        erp发货前汇总.addAll(readFirstSheet(file2022发货前退款截至10月31日, ErpRefundExcelDTO.class, 1, "erp file2022发货前退款截至10月31日"));
//
        erp全部汇总.addAll(EasyExcelUtil.readFirstSheet(file2020全部, ErpRefundExcelDTO.class, 1));
        erp全部汇总.addAll(EasyExcelUtil.readFirstSheet(file2021全部, ErpRefundExcelDTO.class, 1));
        erp全部汇总.addAll(EasyExcelUtil.readFirstSheet(file20220531全部, ErpRefundExcelDTO.class, 1));
        erp全部汇总.addAll(EasyExcelUtil.readFirstSheet(file20221031全部, ErpRefundExcelDTO.class, 1));

        List<CheckOutExcelDTO> checkout数据 = EasyExcelUtil.loopRead(checkout渠道数据文件, CheckOutExcelDTO.class, 1);


        log.info("--------- 校验数据");
        //校验同时写文件
        try (ExcelWriter excelWriter = EasyExcel.write(exportFileName).build()) {
            //写校验sheet
            List<Unit<String>> checkErpAll = check校验erp系统数据正确性(erp发货前汇总, erp发货后汇总, erp全部汇总);
            WriteSheet writeSheet = EasyExcel.writerSheet(0, "erp系统数据校验").head(Unit.class).build();
            excelWriter.write(checkErpAll, writeSheet);
            checkErpAll = null;//释放内存
            //写checkout渠道数据对比sheet
            List<ErpCheckoutExportExcelDTO> erpCheckoutExportExcelDTOS = 校验checkout渠道数据(erp全部汇总, checkout数据);
            WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "校验checkout渠道").head(ErpCheckoutExportExcelDTO.class).build();
            excelWriter.write(erpCheckoutExportExcelDTOS, writeSheet1);
            erpCheckoutExportExcelDTOS = null;
        }




        checkout数据.stream()
                .filter(checkOutExcelDTO -> Objects.equals(checkOutExcelDTO.getReference(), "CSA34151909220925075444869"))
                .collect(Collectors.toList());

        erp全部汇总.stream()
                .filter(erpRefundExcelDTO -> Objects.equals(erpRefundExcelDTO.getOrderNumber(), "CSA34151909220925075444869"))
                .collect(Collectors.toList());

        //数据分组: channel->orderNumber->退款数据项


    }

    /**
     * checkout退款数据特点:   ActionType=Refund  && BreakdownType描述中以Refunded开头,没有Fee字样
     *
     * @param erp全部汇总
     * @param checkout数据
     */
    private static List<ErpCheckoutExportExcelDTO> 校验checkout渠道数据(List<ErpRefundExcelDTO> erp全部汇总, List<CheckOutExcelDTO> checkout数据) {
        List<ErpCheckoutExportExcelDTO> result = new LinkedList<>();

        Map<String, List<ErpRefundExcelDTO>> erpGroup = erp全部汇总.stream()
                .filter(one -> "checkout".equals(one.getPayChannelName()))
                .collect(Collectors.groupingBy(ErpRefundExcelDTO::getOrderNumber));

        Map<String, List<CheckOutExcelDTO>> checkoutGroup = checkout数据.stream()
                .filter(one -> "Refund".equals(one.getActionType())
                        && StringUtils.startsWith(one.getBreakdownType(), "Refunded")
                        && !StringUtils.contains(one.getBreakdownType(), "Fee"))
                .collect(Collectors.groupingBy(CheckOutExcelDTO::getReference));

        log.warn("============ 校验erp退款数据必须在checkout存在, 且金额相等 ================");
        erpGroup.forEach((s, erpDtos) -> {
            if (!checkoutGroup.containsKey(s)) {
                log.warn("订单 {} 在checkout系统中不存在. 单据列表: {}", s, JSON.toJSONString(erpDtos));
                result.addAll(copyErpCheckoutExportExcelDTOs(erpDtos, "订单在checkout系统中不存在", null, null));
            } else {
                StringBuilder info = new StringBuilder();
                List<CheckOutExcelDTO> checkoutDtos = checkoutGroup.get(s);
                //数据条数
                if (erpDtos.size() != checkoutDtos.size()) {
                    info.append("数量不同:erp.size=" + erpDtos.size() + ",checkout.size=" + checkoutDtos.size()).append(" ");
                }
                //总金额
                BigDecimal erpPrice = erpDtos.stream()
                        .map(ErpRefundExcelDTO::getTotalPriceBalance)
                        .map(BigDecimal::new)
                        .reduce(BigDecimal::add)
                        .orElse(BigDecimal.ZERO);
                BigDecimal checkoutPrice = checkoutDtos.stream()
                        .map(CheckOutExcelDTO::getProcessingCurrencyAmount)
                        .map(BigDecimal::new)
                        .reduce(BigDecimal::add)
                        .orElse(BigDecimal.ZERO);
                //如果差额大于1元  erp价格+ , 渠道价格-
                double difPrice = Math.abs(erpPrice.add(checkoutPrice).doubleValue());
                String guess = "";
                if (difPrice > 1.0) {
                    info.append("总金额不同:erp=" + erpPrice.doubleValue() + ",checkout=" + checkoutPrice.doubleValue()).append(" ");
                    if (Math.abs(difPrice - 20.0) < 0.1) {
                        guess = "退货上门取件运费";
                    }
                }
                if (info.length() > 0) {
//                    info.insert(0, "订单 " + s + " ");
//                    info.append("erp数据列表:").append(JSON.toJSONString(erpDtos)).append("  ");
//                    info.append("checkout渠道数据列表:").append(JSON.toJSONString(checkoutDtos));
                    log.warn(info.toString());
                    result.addAll(copyErpCheckoutExportExcelDTOs(erpDtos, info.toString(), Double.toString(difPrice), guess));
                }
            }
        });

        log.warn("============ 校验checkout退款数据必须在erp存在, 且金额相等 ================");

        /*
Reference	Payment Method	Card Type	Card Category	Issuer Country	Merchant Country	MID	Action Type	Action ID	Processed On	Response Code	Response Description	Breakdown Type	Breakdown Date	Processing Currency Amount	Payout Currency Amount	Region
CSA34151909220925075444869	VISA	DEBIT	Consumer	SA	HKG		Authorization	act_nd4ovn3cssk2xhvtjoh52egdla	2022-09-24T23:54:54.077	10000	Approved	Authorization Fee SAR/USD@0.2605095999	2022-09-24T23:54:54.077	-0.3762237	-0.09800989	International
CSA34151909220925075444869	VISA	DEBIT	Consumer	SA	HKG		Authorization	act_nd4ovn3cssk2xhvtjoh52egdla	2022-09-24T23:54:54.077	10000	Approved	Scheme Fixed Fee SAR/USD@0.2605095999	2022-09-24T23:54:54.077	-0.094056	-0.02450249	International
CSA34151909220925075444869	VISA	DEBIT	Consumer	SA	HKG		Capture	act_d23k6eig34xkdm3my3as3slzqm	2022-09-24T23:54:54.871	10000	Approved	Scheme Fixed Fee SAR/USD@0.2605095999	2022-09-24T23:54:54.871	-0.27108	-0.07061894	International
CSA34151909220925075444869	VISA	DEBIT	Consumer	SA	HKG		Capture	act_d23k6eig34xkdm3my3as3slzqm	2022-09-24T23:54:54.871	10000	Approved	Scheme Variable Fee SAR/USD@0.2605095999	2022-09-24T23:54:54.871	-5.87808	-1.53129627	International
CSA34151909220925075444869	VISA	DEBIT	Consumer	SA	HKG		Capture	act_d23k6eig34xkdm3my3as3slzqm	2022-09-24T23:54:54.871	10000	Approved	Premium Fee SAR/USD@0.2605095999	2022-09-24T23:54:54.871	-1.35648	-0.35337606	International
CSA34151909220925075444869	VISA	DEBIT	Consumer	SA	HKG		Capture	act_d23k6eig34xkdm3my3as3slzqm	2022-09-24T23:54:54.871	10000	Approved	RR (5.00%, Release: 2023-03-25) SAR/USD@0.2605095999	2022-09-24T23:54:54.871	-22.608	-5.89	International
CSA34151909220925075444869	VISA	DEBIT	Consumer	SA	HKG		Capture	act_d23k6eig34xkdm3my3as3slzqm	2022-09-24T23:54:54.871	10000	Approved	Captured SAR/USD@0.2605095999	2022-09-24T23:54:54.871	452.16	117.7920207	International
CSA34151909220925075444869	VISA	DEBIT	Consumer	SA	HKG		Capture	act_d23k6eig34xkdm3my3as3slzqm	2022-09-24T23:54:54.871	10000	Approved	SchemeIC SAR/USD@0.2607409035	2022-09-26T05:03:37.446	-7.23456	-1.88634571	International
CSA34151909220925075444869	VISA	DEBIT	Consumer	SA	HKG		Refund	act_gxgn3yeu62tk5minhnols75yie	2022-10-08T06:35:09.206	10000	Approved	Refund Fee SAR/USD@0.2606918985	2022-10-08T06:35:09.206	-0.7519214	-0.19601982	International
CSA34151909220925075444869	VISA	DEBIT	Consumer	SA	HKG		Refund	act_gxgn3yeu62tk5minhnols75yie	2022-10-08T06:35:09.206	10000	Approved	Scheme Fixed Fee SAR/USD@0.2606918985	2022-10-08T06:35:09.206	-0.319566	-0.08330827	International
CSA34151909220925075444869	VISA	DEBIT	Consumer	SA	HKG		Refund	act_gxgn3yeu62tk5minhnols75yie	2022-10-08T06:35:09.206	10000	Approved	Refunded SAR/USD@0.2606918985	2022-10-08T06:35:09.206	-37.8	-9.85415376	International
CSA34151909220925075444869	VISA	DEBIT	Consumer	SA	HKG		Refund	act_hcmlp2yutzu23iwogs7ayfboby	2022-10-09T12:47:11.537	10000	Approved	Refund Fee SAR/USD@0.2606869980	2022-10-09T12:47:11.537	-0.7519368	-0.19602015	International
CSA34151909220925075444869	VISA	DEBIT	Consumer	SA	HKG		Refund	act_hcmlp2yutzu23iwogs7ayfboby	2022-10-09T12:47:11.537	10000	Approved	Scheme Fixed Fee SAR/USD@0.2606869980	2022-10-09T12:47:11.537	-0.319573	-0.08330853	International
CSA34151909220925075444869	VISA	DEBIT	Consumer	SA	HKG		Refund	act_hcmlp2yutzu23iwogs7ayfboby	2022-10-09T12:47:11.537	10000	Approved	Refunded SAR/USD@0.2606869980	2022-10-09T12:47:11.537	-90	-23.46182982	International
         */

        return result;

    }

    private static List<ErpCheckoutExportExcelDTO> copyErpCheckoutExportExcelDTOs(List<ErpRefundExcelDTO> erpDtos, String checResult, String difPrice, String guess) {
        List<ErpCheckoutExportExcelDTO> result = new LinkedList<>();
        for (int i = 0; i < erpDtos.size(); i++) {
            ErpRefundExcelDTO erpDto = erpDtos.get(i);
            ErpCheckoutExportExcelDTO erpCheckoutExportExcelDTO = new ErpCheckoutExportExcelDTO();
            BeanUtils.copyProperties(erpDto, erpCheckoutExportExcelDTO);
            if (i == 0) {
                erpCheckoutExportExcelDTO.addResult(checResult, difPrice, guess);
            }
            result.add(erpCheckoutExportExcelDTO);
        }
        return result;
    }

    private static List<Unit<String>> check校验erp系统数据正确性(List<ErpRefundExcelDTO> former, List<ErpRefundExcelDTO> later, List<ErpRefundExcelDTO> all) {
        List<Unit<String>> result = new LinkedList<>();
        log.warn("======= check校验erp系统退款数据正确性  ===========");
        log.warn("发货前退款数据总数:{}", former.size());
        log.warn("发货后退款数据总数:{}", later.size());
        log.warn("全部退款数据总数:{}, 是否相等:{}", all.size(), all.size() == former.size() + later.size());
        log.warn("-------- 退款前数据必须全部包含在全部中  ----------");
        former.forEach(form -> {
            if (all.contains(form)) {
                //ok
            } else {
                List<ErpRefundExcelDTO> collect = all.stream().filter(erpRefundExcelDTO -> erpRefundExcelDTO.simpleEquals(form)).collect(Collectors.toList());
                if (CollectionUtils.isEmpty(collect)) {
                    log.warn("退款前的数据在全部数据中不存在,且没有基本相同的数据:{}", JSON.toJSONString(form));
                    result.add(Unit.with(form.getOrderNumber() + ":退款前的数据在全部数据中不存在,且没有基本相同的数据"));
                } else {
                    log.warn("退款前的数据在全部数据中不存在,但有相似数据. 当前数据:{}  相似数据:{}", JSON.toJSONString(form), JSON.toJSONString(collect));
                    result.add(Unit.with(form.getOrderNumber() + ":退款前的数据在全部数据中不存在,但有相似数据"));
                }
            }

        });

        log.warn("-------- 同一个订单的退款, 必须是相同的渠道  ----------");
        all.stream()
                .collect(Collectors.groupingBy(ErpRefundExcelDTO::getOrderNumber))
                .forEach((s, erpRefundExcelDTOS) -> {
                    String channel = erpRefundExcelDTOS.get(0).getPayChannelName();
                    StringBuilder errChannels = new StringBuilder();
                    for (ErpRefundExcelDTO one : erpRefundExcelDTOS) {
                        if (!Objects.equals(channel, one.getPayChannelName())) {
                            errChannels.append(one.getPayChannelName()).append(",");
                        }
                    }
                    if (errChannels.length() > 0) {
                        String warn = String.format("订单 %s 有多个退款渠道:%s 时间:%s", s, errChannels.append(channel).toString(), erpRefundExcelDTOS.get(0).getAuditedTime());
                        log.warn(warn);
                        result.add(Unit.with(warn));
                    }
                });
        return result;
    }


    private static BigDecimal parseBigDecimal(String s) {
        if (StringUtils.contains(s, ",")) {
            s = StringUtils.replace(s, ",", "");
        }
        return new BigDecimal(s);
    }


}
